using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using Microsoft.ApplicationBlocks.Data;
using Grebok.Persistence.QueryModel;

namespace Grebok.Persistence.AdoNet
{
    using Grebok.Domain;

	/// <summary>
	/// Summary description for EmployeeDataMapper.
	/// </summary>
	internal class EmployeeDataMapper : IDataMapper<Employee>
	{
        private static readonly string selectEmployeesCommandText = "SELECT EmployeeId, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, Country, PostalCode, HomePhone, Extension, ReportsTo, PhotoPath FROM Employees";
		private static readonly string selectEmployeeByIdCommandText = selectEmployeesCommandText + " WHERE EmployeeId=@EmployeeId";
        private static readonly string insertEmployeeCommandText = "INSERT INTO Employees (LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath) VALUES (@LastName, @FirstName, @Title, @TitleOfCourtesy, @BirthDate, @HireDate, @Address, @City, @Region, @PostalCode, @Country, @HomePhone, @Extension, @Photo, @Notes, @ReportsTo, @PhotoPath)";
        private static readonly string deleteEmployeeByIdCommandText = "DELETE FROM Employees WHERE EmployeeId=@EmployeeId";
        private static readonly string updateEmployeeByIdCommandText = "UPDATE Employees SET LastName = @LastName, FirstName = @FirstName, Title = @Title, TitleOfCourtesy = @TitleOfCourtesy, BirthDate = @BirthDate, HireDate = @HireDate, Address = @Address, City = @City, Region = @Region, PostalCode = @PostalCode, Country = @Country, HomePhone = @HomePhone, Extension = @Extension, Photo = @Photo, Notes = @Notes, ReportsTo = @ReportsTo, PhotoPath = @PhotoPath WHERE EmployeeID = @EmployeeID";
		
        #region IEmployeeDataMapper Members

		public IList<Employee> GetAll()
		{
            List<Employee> employees = new List<Employee>();
			SqlDataReader employeesReader = SqlHelper.ExecuteReader(ProviderHelper.ConnectionString, 
				CommandType.Text, 
				selectEmployeesCommandText);
			while(employeesReader.Read())
			{
				employees.Add(BuildDataObjectFromRawData(employeesReader));
			}
			employeesReader.Close();
			return employees;
		}

		public Employee GetByKey(object employeeId)
		{
			Employee employee = new UnknownEmployee();
			SqlParameter employeeIdParameter = new SqlParameter("@EmployeeId", SqlDbType.Int, 4);
            employeeIdParameter.Value = employeeId;
			SqlDataReader employeesReader = SqlHelper.ExecuteReader(ProviderHelper.ConnectionString, 
				CommandType.Text, 
				selectEmployeeByIdCommandText,
				new SqlParameter[]{employeeIdParameter});
			if(employeesReader.Read())
				employee = BuildDataObjectFromRawData(employeesReader);
			employeesReader.Close();
			return employee;
		}

        public IList<Employee> GetByCriteria(Query query)
        {
            throw new NotImplementedException();
        }

		public void Create(Employee item)
		{
			throw new NotImplementedException();
		}

		public void Delete(Employee item)
		{
            SqlParameter employeeIdParameter = new SqlParameter("@EmployeeId", SqlDbType.Int, 4);
            employeeIdParameter.Value = item.Id;
            SqlHelper.ExecuteNonQuery(ProviderHelper.ConnectionString,
                CommandType.Text,
                deleteEmployeeByIdCommandText,
                new SqlParameter[] { employeeIdParameter });
		}

		public void Update(Employee item)
		{
			throw new NotImplementedException();
		}

		#endregion

		protected virtual Employee BuildDataObjectFromRawData(IDataRecord rawData)
		{
			Employee employee = new EmployeeProxy();
            employee.AddressInfo = new AddressInfo();

			employee.Id = (int) rawData["EmployeeId"];
			employee.FirstName = (string) rawData["FirstName"];
			employee.LastName = (string) rawData["LastName"];
			employee.HireDate = rawData["HireDate"] is DBNull ? DateTime.MinValue : (DateTime) rawData["HireDate"];
			employee.BirthDate = rawData["BirthDate"] is DBNull ? DateTime.MinValue : (DateTime) rawData["BirthDate"];
			employee.JobTitle = rawData["Title"] is DBNull ? string.Empty : (string) rawData["Title"];
			employee.TitleOfCourtesy = rawData["TitleOfCourtesy"] is DBNull ? string.Empty : (string) rawData["TitleOfCourtesy"];
            employee.AddressInfo.Address = rawData["Address"] is DBNull ? string.Empty : (string)rawData["Address"];
            employee.AddressInfo.City = rawData["City"] is DBNull ? string.Empty : (string)rawData["City"];
            employee.AddressInfo.Country = rawData["Country"] is DBNull ? string.Empty : (string)rawData["Country"];
            employee.AddressInfo.PostalCode = rawData["PostalCode"] is DBNull ? string.Empty : (string)rawData["PostalCode"];
            employee.AddressInfo.Region = rawData["Region"] is DBNull ? string.Empty : (string)rawData["Region"];
			employee.HomePhone = rawData["HomePhone"] is DBNull ? string.Empty : (string) rawData["HomePhone"];
			employee.Extension = rawData["Extension"] is DBNull ? string.Empty : (string) rawData["Extension"];
			employee.PhotoPath = rawData["PhotoPath"] is DBNull ? string.Empty : (string) rawData["PhotoPath"];
			employee.Manager = rawData["ReportsTo"] is DBNull ? new MissingEmployee() : GetByKey((int) rawData["ReportsTo"]);
			return employee;
		}

		internal string GetNotesById(int employeeId)
		{
			SqlParameter employeeIdParameter = new SqlParameter("@EmployeeId", SqlDbType.NChar, 5);
			employeeIdParameter.Value = employeeId;
			string notes = (string) SqlHelper.ExecuteScalar(ProviderHelper.ConnectionString, 
				CommandType.Text, 
				"SELECT Notes FROM Employees WHERE EmployeeID=@EmployeeID",
				new SqlParameter[]{employeeIdParameter});
			return notes;
		}

		internal Bitmap GetPhotoById(int employeeId)
		{
			return new Bitmap(0, 0);
		}
	}
}
